﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace Adams.Web.Data.Classifieds
{
    public static class Index
    {
        public static Adams.Web.Models.Classifieds.Index.PageLoad PageLoad()
        {
            var data = new Adams.Web.Models.Classifieds.Index.PageLoad() { 
                Types =  new Models.Classifieds.TypeCollection()
            };

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT * 
FROM Classifieds.Types 
ORDER BY Name

SELECT C.*, (
    SELECT COUNT(*) 
    FROM Classifieds.Postings P 
    WHERE P.CategoryID = C.CategoryID AND P.StatusID = 2 AND DATEDIFF(d, GETDATE(), P.ExpiresOn) >= 0) AS Postings 
FROM Classifieds.Categories C 
ORDER BY C.Name
", CommandType.Text))
            {
                while (dr.Read())
                {
                    var type = DB.InitFromReader<Adams.Web.Models.Classifieds.Type>(dr, new ColMap { NameInDb = "TypeID", NameInObject = "ID" });
                    type.Categories2 = new Adams.Web.Models.Classifieds.CategoryCollection();
                    data.Types.Add(type);
                }
                dr.NextResult();
                while (dr.Read())
                {
                    var cat = DB.InitFromReader<Adams.Web.Models.Classifieds.Category>(dr, new ColMap { NameInDb = "CategoryID", NameInObject = "ID" });
                    data.Types.Where(t => t.ID == dr["TypeID"].DbInt()).FirstOrDefault().Categories2.Add(cat);
                }
            }

            return data;
        }

    }
}